﻿/*
 * sql操作帮助类
 * */
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;

namespace WH.NurseSys.DAL
{
    public class SqlHelper
    {
        // ADO.Net的ExecuteReader的封装    
        public static DataTable ExecuteTable(string sqlText, params IDbDataParameter[] parameters)
        {
            string SqlServDb = ConfigurationManager.ConnectionStrings["nurse"].ConnectionString;
            using (SqlConnection sqlConn = new SqlConnection(SqlServDb))
            {
                sqlConn.Open();
                using (SqlCommand sqlCmd = sqlConn.CreateCommand())
                {
                    sqlCmd.CommandText = sqlText;
                    sqlCmd.Parameters.AddRange(parameters);
                    SqlDataAdapter sqlAdapter = new SqlDataAdapter(sqlCmd);
                    DataSet ds = new DataSet();
                    sqlAdapter.Fill(ds);
                    sqlCmd.Parameters.Clear();
                    if (ds.Tables.Count <= 0)
                    {
                        throw new Exception("ExecuteReader执行的Tables.Count<=0");
                    }
                    return ds.Tables[0];
                }
            }
        }

        // 封装ADO.Net的SqlCommand的ExecuteScalar方法      
        public static object ExecuteScalar(string sqlText, params IDataParameter[] parameters)
        {
            string SqlServDb = ConfigurationManager.ConnectionStrings["nurse"].ConnectionString;
            using (SqlConnection sqlConn = new SqlConnection(SqlServDb))
            {
                sqlConn.Open();
                using (SqlCommand sqlCmd = sqlConn.CreateCommand())
                {
                    sqlCmd.CommandText = sqlText;
                    sqlCmd.Parameters.AddRange(parameters);
                    object scalar = sqlCmd.ExecuteScalar();
                    sqlCmd.Parameters.Clear();
                    return scalar;
                }
            }
        }

        // 封装ADO.Net的SqlCommand的ExecuteNoQuery方法      
        public static int ExecuteNoQuery(string sqlText, params IDataParameter[] parameters)
        {
            string SqlServDb = ConfigurationManager.ConnectionStrings["nurse"].ConnectionString;
            using (SqlConnection sqlConn = new SqlConnection(SqlServDb))
            {
                sqlConn.Open();
                using (SqlCommand sqlCmd = sqlConn.CreateCommand())
                {
                    sqlCmd.CommandText = sqlText;
                    sqlCmd.Parameters.AddRange(parameters);
                    int rows = sqlCmd.ExecuteNonQuery();
                    return rows;
                }
            }
        }

        //处理null值
        public static object ToDbValue(object value)
        {
            if (value == null)
                return DBNull.Value;
            else
                return value;
        }
    }
}
